package model.dba;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Usuario;

public class UsuarioDAO extends JDBC {
	private static UsuarioDAO usuarioDAO;

	private UsuarioDAO() {
		super();
	}

	public static UsuarioDAO getInstance() {
		if (usuarioDAO == null) {
			usuarioDAO = new UsuarioDAO();
		}
		return usuarioDAO;
	}

	public int add(Usuario usuario) {
		try {
			String queryString = "INSERT INTO usuarios(nombre,constrasenia,permisos)" + " VALUES(?,?,?) ";

			preparedStatement = connection.prepareStatement(queryString);

			preparedStatement.setString(1, usuario.getName());
			preparedStatement.setString(2, usuario.getPassword());
			preparedStatement.setString(3, usuario.getPermisos());
			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return getLastId();

	}

	public boolean update(Usuario usuario) {
		boolean state = false;
		try {
			String queryString = "UPDATE usuarios SET nombre=?,constrasenia=?,permisos=? WHERE idUsuario=?";

			preparedStatement = connection.prepareStatement(queryString);

			preparedStatement.setString(1, usuario.getName());
			preparedStatement.setString(2, usuario.getPassword());
			preparedStatement.setString(3, usuario.getPermisos());
			preparedStatement.setLong(4, usuario.getId());

			state = preparedStatement.executeUpdate() != 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();

			}
		}
		return state;
	}

	public boolean delete(Integer id) {
		boolean state = false;
		try {
			String queryString = "DELETE FROM usuarios WHERE idUsuario=?";

			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setInt(1, id);
			state = preparedStatement.executeUpdate() != 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return state;

	}

	public Usuario get(Integer id) {

		Usuario area = null;
		try {
			String queryString = "SELECT * FROM usuarios where idUsuario = '" + id + "';";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				area = populate();

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return area;
	}

	public List<Usuario> find(String nombre) {

		List<Usuario> areaList = new ArrayList<Usuario>();
		try {
			String queryString = "SELECT * FROM usuarios where nombre like '" + nombre + "%' ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				areaList.add(populate());

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return areaList;
	}

	public Usuario get(String nombre) {

		Usuario area = new Usuario();
		try {
			String queryString = "SELECT * FROM usuarios where nombre ='" + nombre + "' ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				area = populate();

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return area;
	}

	public Usuario find(String nombre, String password) {

		// nombre.toLowerCase();
		// password.toLowerCase();
		Usuario areaList = new Usuario();
		try {
			String queryString = "SELECT * FROM usuarios where nombre ='" + nombre + "'  and constrasenia ='" + password + "';";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				areaList = populate();

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return areaList;
	}

	public Usuario findUsuario(String nombre, String password) {

		Usuario usuario = new Usuario();
		try {
			String queryString = "SELECT * FROM usuarios where nombre ='" + nombre + "'  and constrasenia ='" + password + "';";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				usuario = populate();

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return usuario;
	}

	public List<Usuario> all() {

		List<Usuario> areaList = new ArrayList<Usuario>();
		try {
			String queryString = "SELECT * FROM usuarios ORDER BY idusuario ;"; // le agregue ORDER BY idusuario

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				areaList.add(populate());

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return areaList;
	}

	public int getLastId() {
		int ret = 0;
		try {
			String queryString = "SELECT idUsuario FROM usuarios order by idUsuario DESC LIMIT 1  ";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				ret = resultSet.getInt("idUsuario");

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return ret;

	}

	private Usuario populate() throws SQLException {

		Usuario area = new Usuario();
		area.setId((int) resultSet.getLong("idUsuario"));

		area.setName(resultSet.getString("nombre"));
		area.setPassword(resultSet.getString("constrasenia"));
		area.setPermisos(resultSet.getString("permisos"));

		return area;

	}
}